Release 10.1A: OpenEdge Data Management:
SQL Reference

Unsubscripted array references

When there is a reference to an unsubscripted Progress 4GL array column, OpenEdge SQL performs these operations:

Extracts each element from the Progress 4GL array.
Converts the element to a DT_VARCHAR data type.
Passes the element to the PRO_ARR_ESCAPE function for the addition of any necessary escape characters.
Concatenates the result to a composite with the separator character ( ; ) between this element and the next element.

This results in dual data types for array fields. The fetch type is TPE_DT_NVARCHAR for the unsubscripted references. The underlying Progress 4GL data type is the native type.

Examples

The first example assumes a character array named ARRAYCHAR containing three elements in a row in the customer table where the cust_num column is equal to 88, and with values:

ARRAYCHAR[ 1 ] = 'aa'

ARRAYCHAR[ 2 ] = 'bb'

ARRAYCHAR[ 3 ] = 'cc'

The ARRAYCHAR example returns the value 'aa;bb;cc':

SELECT arraychar FROM customer WHERE cust_num = 88 ; ARRAYCHAR --------- aa;bb;cc 1 record selected

To retrieve an individual element from an array with any escape characters removed, use the PRO_ELEMENT and PRO_ARR_DESCAPE functions. For example, ARRAYTYPE[ 2 ] contains the value 'aa;bb'. The PRO_ELEMENT function in the ARRAYTYPE example returns 'aa~;bb' and PRO_ARR_DESCAPE('aa~;bb') removes the escape character ( ~ ), returning the element value 'aa;bb':

select pro_arr_descape(pro_element(arraytype,2,2)) from customer ; ARRAYTYPE --------- aa;bb 1 record selected